# coding=utf-8

import web
from web.utils import storage

from db import connection

db = connection.default

def get_discounts_valid_without_top10():
    """
    获取所有有效的（未过期），排除掉当期Top10的列表
    """
    where = ''
    # 获取当期top10的id
    last = get_last_discount_top10(1)
    if last:
        where = ' AND di.id NOT IN (%s)' % last[0].discount_info_ids

    # 获取结果
    sets = db.query('''
            SELECT di.id,di.title,di.lightning,di.directing,di.price,
                di.is_initial_price,di.price_index,
                p.name AS partner_name 
            FROM discount_info AS di
                INNER JOIN
                partner AS p
                    ON p.id = di.partner_id
            WHERE di.expire > NOW()
                %s
            ''' % where)
    return storage(lst = [d for d in sets]) 

def get_discounts_directing():
    """
    获取所有未过期直销特惠
    """
    sets = db.query('''
            SELECT di.id,di.title,di.lightning,di.directing,di.price,
                di.is_initial_price,di.price_index,
                p.name AS partner_name 
            FROM discount_info AS di
                INNER JOIN
                partner AS p
                    ON p.id = di.partner_id
            WHERE di.expire > NOW()
                AND di.directing = 1;
            ''')
    return [storage(lst = [d for d in sets])] 

def get_discounts_lightning_directing():
    """
    获取所有未过期的闪电及直销特惠
    """
    sets = db.query('''
            SELECT di.id,di.title,di.lightning,di.directing,di.price,
                di.is_initial_price,di.price_index,
                p.name AS partner_name 
            FROM discount_info AS di
                INNER JOIN
                partner AS p
                    ON p.id = di.partner_id
            WHERE di.expire > NOW()
                AND (di.lightning = 1 OR di.directing = 1);
            ''')
    return [storage(lst = [d for d in sets])]

def get_discounts_lightning():
    """
    获取所有未过期的闪电特惠
    """
    sets = db.query('''
            SELECT di.id,di.title,di.lightning,di.directing,di.price,
                di.is_initial_price,di.price_index,
                p.name AS partner_name 
            FROM discount_info AS di
                INNER JOIN
                partner AS p
                    ON p.id = di.partner_id
            WHERE di.expire > NOW()
                AND di.lightning = 1;
            ''')
    return [storage(lst = [d for d in sets])]

def increment(fid, name, value = 1):
    """递增特惠信息的统计信息"""
    return db.query('''
            INSERT INTO discount_statistics SET id =
                $fid, %(name)s = $value 
            ON DUPLICATE KEY UPDATE %(name)s = %(name)s + $value;
            ''' % {'name': name}
            , vars = locals())

def get_discount_url(fid):
    """读取特惠信息的url"""
    sets = db.select('discount_info'
            , what = 'url'
            , where = 'id = $fid'
            , vars = locals())
    if sets:
        return sets[0].url
    return None

def get_last_TOP10_discount_ids():
    """
    获取最新一起甄选记录的特惠信息内码列表
    """
    tset = get_last_discount_top10(1)
    if tset:
        return tset[0].discount_info_ids.split(',')
    return None

def get_last_discount_top10_by_date(date):
    """
    获取给定日期的甄选信息
    """
    sets = db.select('discount_top10'
            , where = 'date_index = $date'
            , vars = locals())
    if sets:
        return [t for t in sets]
    return None

def get_last_TOP10_rand(top = 5):
    """
    从最新的一起甄选TOP10中，随机选出给定条数的记录
    最大不能超过10条
    """
    # 最大读取不超过10条
    top = min(top, 10)
    # 获取最新的一条甄选TOP10信息
    tset = get_last_discount_top10(1)
    if tset:
        import random
        # 将其中的id转换为列表
        idlst = tset[0].discount_info_ids.split(',')
        # top最大不能超过列表长度
        top = min(top, len(idlst))
        # 获取一次随机列表
        idlst = random.sample(idlst, top)
        # 读取这些id的特惠信息
        posts = get_discounts_by_ids_with_dest(','.join(idlst))
        if posts:
            # 为了体现出随机性
            # 这里不使用数据库返回的顺序
            # 对返回得列表再随机一次
            # posts在这里是一个只能向前读取的对象
            # 所以这里转换为了列表
            lst = [post for post in posts]
            # 生成随机顺序的列表返回结果
            # 这里没有维持idlst的顺序
            # 反正是随机，所以无所谓啦
            return random.sample(lst, len(lst))

    # 走到这里就杯具了
    return None
        

def get_last_TOP10(top = 1, type = 0, date = 0, depart = 0, lightning = False, include_content = False):
    """
    获取最新的【甄选TOP10】信息
    """
    if date > 0:
        # 获取给定日期的甄选记录
        tset = get_last_discount_top10_by_date(date)
    else:
        # 获取最新设置的前top条甄选记录
        tset = get_last_discount_top10(top)
    if tset:
        # 将设置的的信息收集起来
        idlst = [t.discount_info_ids for t in tset if t.discount_info_ids]
        if idlst:
            # 组合成一条字符串
            # "这里为了可读性，没有将两条语句合并在一起写
            idlst = ','.join(idlst)
            # 读取出这些id的特惠信息记录
            posts = get_discounts_by_ids_with_dest(idlst
                        , type
                        , depart
                        , lightning
                        , include_content)
            # 转换为词典，便于按id取出每条特惠信息记录
            if posts:
                # 转换为词典
                pdict = { post.id: post for post in posts }
                # 创建返回列表
                retlst = []
                # 处理结果中的所有甄选信息
                while tset:
                    # 从特惠甄选信息中取出一条
                    dist = tset.pop(0)
                    # 切割设置的特惠信息id
                    if dist.discount_info_ids:
                        # 创建当期列表
                        ids = dist.discount_info_ids.split(',')
                        # 添加当前设置的特惠信息列表到返回结果集中
                        lst = [pdict[int(id)] for id in ids if int(id) in pdict]
                        # "以上代码为了可读性没有合并为一句代码
                        # 列表按性价比排序
                        retlst.append(storage(date = dist.date_index
                            , lst = sorted(lst
                                        , key = lambda p: p.price_index
                                        , reverse = True)
                            ))

                # 返回结果
                # 前台调用此接口后，可以通过列表索引，
                # 获取对应周数的甄选特惠信息列表
                return retlst

    # 到这里处理失败了
    # 1) 可能没有设置甄选信息
    # 2) 可能甄选信息中没有设置特惠信息
    return None

def get_discounts_by_ids_with_dest(ids, type = 0, depart = 0, lightning = False, include_content = False):
    """
    获取给定id列表的特惠信息（包含目的地名）
    """
    if ids:
        att_where = ''
        att_proj_where = ''
        if type:
            #att_from = '''INNER JOIN 
            #              travel_proj AS tp 
            #                ON tp.id = di.proj_id AND tp.type = $type
            #        '''
            att_proj_where = ' AND tp.type = $type'
        # 给定了出发城市
        if depart:
            if isinstance(depart, (int, long)):
                att_where = ' AND di.depart_id = $depart'
            elif isinstance(depart, tuple):
                att_where = ' AND di.depart_id IN $depart'
        # 闪电特惠
        if lightning:
            att_where = ' AND di.lightning = 1'

        att_from = ''
        att_fields = ''
        if include_content:
            # 要在返回结果中包含特惠信息的内容
            # 那这里就要再联合detail表
            att_from = '''INNER JOIN
                          discount_detail_info AS ddi 
                            ON ddi.id = di.id
                    '''
            att_fields = ',ddi.content'

        return db.query('''
                SELECT di.id,di.title,di.price,di.discount,
                        di.media_id,di.price_index,di.is_initial_price,
                        di.lightning,di.directing,
                        IFNULL(dest.name, tp.name) AS dest_name,p.name AS partner_name, 
                        tp.type%(fields)s
                FROM discount_info AS di
                    INNER JOIN
                    partner AS p
                        ON p.id = di.partner_id
                    INNER JOIN
                    travel_proj AS tp
                        ON tp.id = di.proj_id%(proj_where)s
                    LEFT OUTER JOIN
                    travel_dest AS dest
                        ON dest.id = tp.dest_id
                    %(from)s
                WHERE di.id IN (%(ids)s)%(where)s
                ORDER BY di.id DESC;
            ''' % {
                    'fields':att_fields,
                    'from':att_from,
                    'proj_where':att_proj_where,
                    'ids':ids,
                    'where':att_where,}
            , vars = locals())
    return None

def get_discounts_by_ids_with_partner(ids):
    """
    获取给定id列表的特惠信息（包含合作商家名）
    """
    if ids:
        return db.query('''
                SELECT di.id,di.title,di.price,di.media_id,
                        p.name AS partner_name 
                FROM discount_info AS di
                    INNER JOIN
                    partner AS p
                        ON p.id = di.partner_id
                WHERE di.id IN (''' + ids + ''')
                ORDER BY di.id DESC;
            ''')
    return None

def get_last_discount_top10(top = 10):
    """
    获取最新的前@top条甄选TOP10信息
    """
    sets = db.select('discount_top10', order = 'date_index DESC'
            , limit = top)
    return [r for r in sets]

def get_discount_redirect(id):
    """
    获取特惠信息的抓项页所需的数据
    """
    sets = db.query('''
            SELECT di.url,p.name,p.name AS partner_name,
                p.domain,p.spread_url
            FROM discount_info AS di
                INNER JOIN
                partner AS p
                    ON p.id = di.partner_id
            WHERE di.id = $id;
            '''
            , vars = locals())
    return sets and sets[0] or None

def get_discount(id):
    """
    获取给定特惠信息内容
    """
    sets = db.query('''SELECT di.id,di.title,di.price,di.url,di.price_index,
                            di.media_id,di.partner_id,di.is_initial_price,
                            di.depart_id,di.expire,di.lightning,
                            di.date_created,di.directing,
                            ddi.content,ddi.booking_tips,tp.type
                     FROM discount_info AS di
                        INNER JOIN
                        discount_detail_info AS ddi
                            ON ddi.id = di.id
                        INNER JOIN
                        travel_proj AS tp
                            ON tp.id = di.proj_id
                     WHERE di.id = $id'''
                     , vars = locals())
    if sets:
        return sets[0]
    return None

def get_topN_discounts(top = 10):
    """
    获取前N条最新的特惠信息
    @top as int, 获取的条数，默认10
    """
    return db.query('''SELECT dest.name AS dest_name,
                            di.id,di.title,
                            di.short_text,di.media_id,
                            di.price 
                     FROM discount_info AS di
                        INNER JOIN
                        travel_dest AS dest 
                            ON dest.id = di.dest_id
                     ORDER BY di.id DESC 
                     LIMIT $top;'''
                     , vars = locals())

def get_discounts_for_index(offset, length):
    """
    获取首页所需的特惠信息
    """
    sets = db.query('''SELECT SQL_CALC_FOUND_ROWS dest.name,
                        di.id,di.title,
                        di.short_text,di.media_id 
                     FROM discount_info AS di
                        INNER JOIN
                        travel_dest AS dest
                            ON dest.id = di.dest_id
                     ORDER BY di.id DESC
                     LIMIT $offset, $length;'''
                     , vars = locals())
    totals = db.query('SELECT FOUND_ROWS() AS totals;')
    return storage(result = sets, totalrecords = totals[0].totals)
